package com.example.covid19.mapper;

import com.example.covid19.entity.Builder;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author BaiZhengChun
 * @since 2023-03-07
 */
public interface BuilderMapper extends BaseMapper<Builder> {
    /**
     *返回当前寝室楼住了多少人
     */
    @Select("SELECT COUNT(s.zid) AS student_count " +
            "FROM builder b " +
            "LEFT JOIN room r ON b.lid = r.lid " +
            "LEFT JOIN student s ON r.id = s.rid " +
            "WHERE b.lid = #{lid} " +
            "GROUP BY b.lid, b.build_name; ")
    Integer getbuilderCount(@Param("lid")Integer lid);
    /**
     * 计算当前楼的入住率
     */
    @Select("SELECT " +
            " (COUNT(s.zid) / SUM(r.specification)) * 100 AS occupancy_rate " +
            "FROM " +
            " builder AS b " +
            " JOIN " +
            " room AS r " +
            " ON " +
            " b.lid = r.lid " +
            " JOIN " +
            " student AS s " +
            " ON " +
            " r.id = s.rid " +
            " WHERE " +
            " b.lid = #{lid} " +
            " GROUP BY " +
            " b.lid, " +
            " b.build_name;")
    String getodds(@Param("lid") Integer lid);
    @Select("SELECT " +
            " SUM(room.specification) AS sumer " +
            "FROM " +
            " room " +
            " INNER JOIN " +
            " builder " +
            " ON " +
            " room.lid = builder.lid " +
            "WHERE " +
            " builder.lid = #{lid} " +
            "GROUP BY " +
            " builder.lid")
    Integer getsum(@Param("lid") Integer lid);


}
